iT邦幫忙

2022 iThome 鐵人賽
DAY 20
0
Software Development

ClickHouse:時序資料庫建置與運行系列 第 20 篇

day20-SQL使用與操作方法介紹(十一)

14th鐵人賽
1008 瀏覽

  • 分享至 

  • xImage
    •  

前言

本章節，將會繼續把ClickHouse資料庫的各式SQL語法使用與範例進行說明與展示。

RENAME語句

我們可以使用該語句來對資料庫、資料表與字典（Dictionary）等進行重新命名的動作，相關的語法使用方法如下：

RENAME DATABASE|TABLE|DICTIONARY name TO new_name [,...] [ON CLUSTER cluster]

若要對資料庫名稱進行修改，則可以使用下列的語法達成：

RENAME DATABASE atomic_database1 TO atomic_database2 [,...] [ON CLUSTER cluster]

若要對資料表進行重新命名，則可以使用下列的語法達成：

RENAME TABLE [db1.]name1 TO [db2.]name2 [,...] [ON CLUSTER cluster]

從上述的語法的使用可以知道，進行重新命名可以針對一個或多個進行重新命名，執行上述的SQL語句範例如下：

RENAME TABLE table_A TO table_A_bak, table_B TO table_B_bak;

我們也可以對字典（dictionary）進行重新命名，相關的語法使用如下：

RENAME DICTIONARY [db0.]dict_A TO [db1.]dict_B [,...] [ON CLUSTER cluster]

字典（dictionary）是一個對應 (鍵→值)的儲存資料方式，這對各種類型的參考列表很方便。

ClickHouse資料庫支援了特殊函數來操作字典，並可以用在SQL查詢的語句中，使用這些函數來使用字典可以比使用JOIN資料表的方式更輕易與有效率。

ClickHouse資料庫分別支援了外部與內部的字典。

EXCHANGE語句

此語句只支援Atomic資料庫引擎，使用這個語句可以讓我們設定兩個資料表或字典的資料進行互換，同時也會在互換的過程中執行RENAME的SQL語句，相關的語法使用如下：

EXCHANGE TABLES|DICTIONARIES [db0.]name_A AND [db1.]name_B [ON CLUSTER cluster]

若我們要將兩個資料表的資料進行互換時，可以參考與使用下列的語法：

EXCHANGE TABLES [db0.]table_A AND [db1.]table_B [ON CLUSTER cluster]

上述的SQL語句範例執行與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) select * from example_back;

SELECT *
FROM example_back

Query id: 75e5adcb-84eb-4c16-b999-763f582e9957

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘

3 rows in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) select * from describe_example;

SELECT *
FROM describe_example

Query id: ec53e4e6-c406-4629-a29d-16e218bc10a2

Ok.

0 rows in set. Elapsed: 0.003 sec.
ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) exchange tables describe_example and example_back;

EXCHANGE TABLES describe_example AND example_back

Query id: f34e9cfd-868e-4fd4-b6e4-f939467041a7

Ok.

0 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) select * from describe_example;

SELECT *
FROM describe_example

Query id: 17e7d0f0-9690-45e5-b9da-32a1671d5feb

┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             2 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           1 │             1 │     2 │             3 │
└─────────────┴───────────────┴───────┴───────────────┘
┌─primary_key─┬─secondary_key─┬─value─┬─partition_key─┐
│           0 │             0 │     0 │             0 │
└─────────────┴───────────────┴───────┴───────────────┘

3 rows in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) select * from example_back;

SELECT *
FROM example_back

Query id: c7146d1f-dabe-4291-b9ad-a309c71af8ac

Ok.

0 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

若我們要將兩個字典的資料進行互換，則可以參考與使用下列的語法：

EXCHANGE DICTIONARIES [db0.]dict_A AND [db1.]dict_B [ON CLUSTER cluster]

SET語句

使用這個語句是用來將值設定給某個ClickHouse資料庫系統的設定，相關的語法使用如下：

SET param = value

使用上述的語法只可以針對當前的資料庫連線（session）的設定進行改變，無法透過這個語句去改變既有資料庫設定，即本來在資料庫設定檔裡面的值。因此當這個資料庫連線中斷後，下一個資料庫連線中，相關使用上述的語句所修改的設定又會回到本來的設定值。

SET ROLE語句

將角色指派給指定的使用者們，相關的語法使用如下：

SET ROLE {DEFAULT | NONE | role [,...] | ALL | ALL EXCEPT role [,...]}

我們可以透過下列的語句將預設的角色設定給指定的使用者，相關的語法使用如下：

SET DEFAULT ROLE {NONE | role [,...] | ALL | ALL EXCEPT role [,...]} TO {user|CURRENT_USER} [,...]

我們可以透過下列的方式將多個預設的角色設定給指定的使用者，相關的語法範例如下：

SET DEFAULT ROLE role1, role2, ... TO user

我們也可以設定將所有角色設定給指定的使用者，相關的語法範例如下：

SET DEFAULT ROLE ALL TO user

也可以將預設角色設定從指定的使用者移除，相關的語法範例如下：

SET DEFAULT ROLE NONE TO user

也可以將除了role1與role2角色設定之外的所有預設角色設定給指定的使用者，相關的語法範例如下：

SET DEFAULT ROLE ALL EXCEPT role1, role2 TO user

TRUNCATE語句

該語句可以讓我們將指定的資料表的資料進行清空，若有加上IF EXISTS從句的話，則會判斷要進行清空資料表存不存在，存在的話會進行清空動作，不存在的話則不會跳出任何錯誤的訊息，相關的語法使用如下：

TRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]

此語句不支援View、File、URL、Buffer與Null等資料表引擎，我們可以設定replication_alter_partitions_sync設定來等待各個資料複製點（replicas）的清空資料動作。

我們也可以指定replication_wait_for_inactive_replica_timeout設定來等待尚未活躍（inactive）的資料複製點執行該清空資料語句的動作。

USE語句

這個語句就是用來切換當前的資料庫，相關的語法使用如下：

USE db

這個語句無法在使用HTTP協定下的連線進行使用，只能用於ClickHouse資料庫客戶端指令的方式連線時使用。

EXPLAIN語句

此語句是用來將SQL描述句執行的過程給顯示出來，相關的語法使用如下：

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE | ESTIMATE | TABLE OVERRIDE] [setting = value, ...]
    [
      SELECT ... |
      tableFunction(...) [COLUMNS (...)] [ORDER BY ...] [PARTITION BY ...] [PRIMARY KEY] [SAMPLE BY ...] [TTL ...]
    ]
    [FORMAT ...]

上述的語法範例如下：

EXPLAIN SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;

執行上述的語法之後，得到的輸出訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN SELECT sum(number) FROM numbers(10) UNION ALL SELECT sum(number) FROM numbers(10) ORDER BY sum(number) ASC FORMAT TSV;

EXPLAIN
SELECT sum(number)
FROM numbers(10)
UNION ALL
SELECT sum(number)
FROM numbers(10)
ORDER BY sum(number) ASC
FORMAT TSV

Query id: 216acfd0-fb00-4c37-b9d9-acdfb3384fc0

Union
  Expression ((Projection + Before ORDER BY))
    Aggregating
      Expression (Before GROUP BY)
        ReadFromStorage (SystemNumbers)
  Expression (Projection)
    Sorting (Sorting for ORDER BY)
      Expression (Before ORDER BY)
        Aggregating
          Expression (Before GROUP BY)
            ReadFromStorage (SystemNumbers)

11 rows in set. Elapsed: 0.003 sec.

EXPLIAN語句有幾種類型，分別如下：

  • AST — 抽象的語法樹。
  • SYNTAX — 在優化AST語法後的語法。
  • PLAN — 執行查詢的方法過程。
  • PIPELINE — 執行查詢方法的管線過程。

AST類型可以將SQL查詢語法輸出成語法樹，使用此類型不限於使用SELECT語句，相關的語法範例如下：

EXPLAIN AST SELECT 1;

相關執行上述的語法輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN AST SELECT 1;

EXPLAIN AST
SELECT 1

Query id: c73822b5-a613-4278-aa23-014c57dbbcd0

┌─explain───────────────────────────┐
│ SelectWithUnionQuery (children 1) │
│  ExpressionList (children 1)      │
│   SelectQuery (children 1)        │
│    ExpressionList (children 1)    │
│     Literal UInt64_1              │
└───────────────────────────────────┘

5 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

另外一個範例如下：

EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();

相關執行上述的範例所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN AST ALTER TABLE t1 DELETE WHERE date = today();

EXPLAIN AST
ALTER TABLE t1
    DELETE WHERE date = today()

Query id: 2ec4b8d4-d2a7-4599-9ae4-e55d75ad1279

┌─explain────────────────────────────┐
│ AlterQuery  t1 (children 2)        │
│  ExpressionList (children 1)       │
│   AlterCommand DELETE (children 1) │
│    Function equals (children 1)    │
│     ExpressionList (children 2)    │
│      Identifier date               │
│      Function today (children 1)   │
│       ExpressionList               │
│  Identifier t1                     │
└────────────────────────────────────┘

9 rows in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

若要得到將AST語法樹進行優化後輸出的語法結果，可以使用SYNTAX類型來達成，相關的範例如下：

EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c;

執行上述的語法後所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN SYNTAX SELECT * FROM system.numbers AS a, system.numbers AS b, system.numbers AS c;

EXPLAIN SYNTAX
SELECT *
FROM system.numbers AS a, system.numbers AS b, system.numbers AS c

Query id: bcf1861f-311c-418c-9011-ef7dcf1afa9e

┌─explain────────────────────────────┐
│ SELECT                             │
│     `--a.number` AS `a.number`,    │
│     `--b.number` AS `b.number`,    │
│     number AS `c.number`           │
│ FROM                               │
│ (                                  │
│     SELECT                         │
│         number AS `--a.number`,    │
│         b.number AS `--b.number`   │
│     FROM system.numbers AS a       │
│     CROSS JOIN system.numbers AS b │
│ ) AS `--.s`                        │
│ CROSS JOIN system.numbers AS c     │
└────────────────────────────────────┘

13 rows in set. Elapsed: 0.004 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

若要得到SQL語句的執行步驟，則可以搭配PLAN的類型使用，相關的設定如下：

  • header — 輸出那個步驟的欄位名稱，將此稱為標頭，預設值為0。
  • description — 輸出該步驟的描述訊息，預設值為1。
  • indexes — 顯示出使用到的索引，即顯示用到的每個索引的過濾部分數與過濾顆粒（granules ）數，預設值為0。
    • 此設定支援MergeTree的資料表引擎。
  • actions — 列出有關於查詢方法步驟的更詳細的資訊，預設值為0。
  • json — 將每個查詢的步驟以JSON方式輸出，預設值為0。
    • 在輸出查詢步驟的格式建議以TSVRaw格式輸出以避免不必要的逃脫字元的問題。

相關的語法範例如下：

EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;

執行上述的語法所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN SELECT sum(number) FROM numbers(10) GROUP BY number % 4;

EXPLAIN
SELECT sum(number)
FROM numbers(10)
GROUP BY number % 4

Query id: 33fed42e-8e9f-4810-9589-a22920703148

┌─explain─────────────────────────────────────┐
│ Expression ((Projection + Before ORDER BY)) │
│   Aggregating                               │
│     Expression (Before GROUP BY)            │
│       ReadFromStorage (SystemNumbers)       │
└─────────────────────────────────────────────┘

4 rows in set. Elapsed: 0.004 sec.

當設定json=1與description=0的設定時，可以參考下列的語法範例：

EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

執行上述相關的語句後所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN json = 1, description = 0 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

EXPLAIN json = 1, description = 0
SELECT 1
UNION ALL
SELECT 2
FORMAT TSVRaw

Query id: e5c0df84-ca70-44df-8893-ca2392262283

[
  {
    "Plan": {
      "Node Type": "Union",
      "Plans": [
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "ReadFromStorage"
            }
          ]
        },
        {
          "Node Type": "Expression",
          "Plans": [
            {
              "Node Type": "ReadFromStorage"
            }
          ]
        }
      ]
    }
  }
]

1 row in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

若將上述的語法範例的description=0改成description=1，則執行語句所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN json = 1, description = 1 SELECT 1 UNION ALL SELECT 2 FORMAT TSVRaw;

EXPLAIN json = 1, description = 1
SELECT 1
UNION ALL
SELECT 2
FORMAT TSVRaw

Query id: 8e40ea3d-714c-42ad-bb01-c1ca1d700452

[
  {
    "Plan": {
      "Node Type": "Union",
      "Plans": [
        {
          "Node Type": "Expression",
          "Description": "(Conversion before UNION + (Projection + Before ORDER BY))",
          "Plans": [
            {
              "Node Type": "ReadFromStorage",
              "Description": "SystemOne"
            }
          ]
        },
        {
          "Node Type": "Expression",
          "Description": "(Conversion before UNION + (Projection + Before ORDER BY))",
          "Plans": [
            {
              "Node Type": "ReadFromStorage",
              "Description": "SystemOne"
            }
          ]
        }
      ]
    }
  }
]

1 row in set. Elapsed: 0.004 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

若將header=1設定在上述的語句的話，則會將欄位名稱以陣列方式輸出在JSON結果中，相關的語法範例如下：

EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;

執行上述的SQL語句的輸出訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN json = 1, description = 0, header = 1 SELECT 1, 2 + dummy;

EXPLAIN json = 1, description = 0, header = 1
SELECT
    1,
    2 + dummy

Query id: b7235a0b-4e05-4aad-9f22-6d3b9f00bf0e

┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ [
  {
    "Plan": {
      "Node Type": "Expression",
      "Header": [
        {
          "Name": "1",
          "Type": "UInt8"
        },
        {
          "Name": "plus(2, dummy)",
          "Type": "UInt16"
        }
      ],
      "Plans": [
        {
          "Node Type": "ReadFromStorage",
          "Header": [
            {
              "Name": "dummy",
              "Type": "UInt8"
            }
          ]
        }
      ]
    }
  }
] │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

當將上述的語句加入indexes = 1設定時，索引的鍵是會被加入的，會包含使用的索引陣列，每個索引都會以JSON類型的陣列進行描述，裡面的鍵值類型有MinMax、Partition、PrimaryKey或Skip，以及其他選擇性的鍵值，相關的欄位名稱敘述如下：

  • Name — 索引名稱，這個只會用於Skip index。
  • Keys — 會以一個欄位的陣列表示，裡面是哪些欄位會用到的索引。
  • Condition — 哪一個條件會被使用到，以字串表示。
  • Description — 一個索引，這個只會用於Skip index。
  • Initial Parts — 使用索引之前的多個部分（parts）。
  • Selected Parts — 使用索引之後的多個部分（parts）。
  • Initial Granules — 使用索引之前的顆粒度（granules）。
  • Selected Granulesis — 使用索引之後的顆粒度（granules）。

相關的範例輸出如下：

"Node Type": "ReadFromMergeTree",
"Indexes": [
  {
    "Type": "MinMax",
    "Keys": ["y"],
    "Condition": "(y in [1, +inf))",
    "Initial Parts": 5,
    "Selected Parts": 4,
    "Initial Granules": 12,
    "Selected Granules": 11
  },
  {
    "Type": "Partition",
    "Keys": ["y", "bitAnd(z, 3)"],
    "Condition": "and((bitAnd(z, 3) not in [1, 1]), and((y in [1, +inf)), (bitAnd(z, 3) not in [1, 1])))",
    "Initial Parts": 4,
    "Selected Parts": 3,
    "Initial Granules": 11,
    "Selected Granules": 10
  },
  {
    "Type": "PrimaryKey",
    "Keys": ["x", "y"],
    "Condition": "and((x in [11, +inf)), (y in [1, +inf)))",
    "Initial Parts": 3,
    "Selected Parts": 2,
    "Initial Granules": 10,
    "Selected Granules": 6
  },
  {
    "Type": "Skip",
    "Name": "t_minmax",
    "Description": "minmax GRANULARITY 2",
    "Initial Parts": 2,
    "Selected Parts": 1,
    "Initial Granules": 6,
    "Selected Granules": 2
  },
  {
    "Type": "Skip",
    "Name": "t_set",
    "Description": "set GRANULARITY 2",
    "Initial Parts": 1,
    "Selected Parts": 1,
    "Initial Granules": 2,
    "Selected Granules": 1
  }
]

當加入了actions=1的設定到EXPLAIN語句後，則會加入每個步驟類型的動作名稱與描述訊息，相關的語法範例如下：

EXPLAIN json = 1, actions = 1, description = 0 SELECT 1 FORMAT TSVRaw;

執行上述的語句所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN json = 1, actions = 1, description = 0 SELECT 1 FORMAT TSVRaw;

EXPLAIN json = 1, actions = 1, description = 0
SELECT 1
FORMAT TSVRaw

Query id: 742fff77-6795-42f1-b1ca-2357e65a9147

[
  {
    "Plan": {
      "Node Type": "Expression",
      "Expression": {
        "Inputs": [],
        "Actions": [
          {
            "Node Type": "COLUMN",
            "Result Type": "UInt8",
            "Result Name": "1",
            "Column": "Const(UInt8)",
            "Arguments": [],
            "Removed Arguments": [],
            "Result": 0
          }
        ],
        "Outputs": [
          {
            "Name": "1",
            "Type": "UInt8"
          }
        ],
        "Positions": [0],
        "Project Input": true
      },
      "Plans": [
        {
          "Node Type": "ReadFromStorage"
        }
      ]
    }
  }
]

1 row in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

若使用EXPLAIN PIPELINE語句有幾個設定可以使用，相關的設定如下：

相關的語法範例如下：

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;

執行上述的語句的輸出訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;

EXPLAIN PIPELINE
SELECT sum(number)
FROM numbers_mt(100000)
GROUP BY number % 4

Query id: 79b8a2b9-cf70-4fc1-a053-3bfc92272d52

┌─explain───────────────────────┐
│ (Expression)                  │
│ ExpressionTransform × 4       │
│   (Aggregating)               │
│   Resize 4 → 4                │
│     AggregatingTransform × 4  │
│       (Expression)            │
│       ExpressionTransform × 4 │
│         (ReadFromStorage)     │
│         NumbersMt × 4 0 → 1   │
└───────────────────────────────┘

9 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

當設定header=1與graph=1設定所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN PIPELINE header=1, graph=1 SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;

EXPLAIN PIPELINE header = 1, graph = 1
SELECT sum(number)
FROM numbers_mt(100000)
GROUP BY number % 4

Query id: 35962379-d09d-41d4-be8b-e5e53f0cfd13

┌─explain────────────────────────────────────────┐
│ digraph                                        │
│ {                                              │
│   rankdir="LR";                                │
│   { node [shape = rect]                        │
│         n1 [label="NumbersMt × 4"];            │
│     subgraph cluster_0 {                       │
│       label ="Expression";                     │
│       style=filled;                            │
│       color=lightgrey;                         │
│       node [style=filled,color=white];         │
│       { rank = same;                           │
│         n2 [label="ExpressionTransform × 4"];  │
│       }                                        │
│     }                                          │
│     subgraph cluster_1 {                       │
│       label ="Expression";                     │
│       style=filled;                            │
│       color=lightgrey;                         │
│       node [style=filled,color=white];         │
│       { rank = same;                           │
│         n5 [label="ExpressionTransform × 4"];  │
│       }                                        │
│     }                                          │
│     subgraph cluster_2 {                       │
│       label ="Aggregating";                    │
│       style=filled;                            │
│       color=lightgrey;                         │
│       node [style=filled,color=white];         │
│       { rank = same;                           │
│         n3 [label="AggregatingTransform × 4"]; │
│         n4 [label="Resize"];                   │
│       }                                        │
│     }                                          │
│   }                                            │
│   n1 -> n2 [label="× 4                         │
│ number UInt64 UInt64(size = 0)"];              │
│   n2 -> n3 [label="× 4                         │
│ number UInt64 UInt64(size = 0)                 │
│ modulo(number, 4) UInt8 UInt8(size = 0)"];     │
│   n3 -> n4 [label="× 4                         │
│ modulo(number, 4) UInt8 UInt8(size = 0)        │
│ sum(number) UInt64 UInt64(size = 0)"];         │
│   n4 -> n5 [label="× 4                         │
│ modulo(number, 4) UInt8 UInt8(size = 0)        │
│ sum(number) UInt64 UInt64(size = 0)"];         │
│ }                                              │
└────────────────────────────────────────────────┘

46 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

當設定header=1、graph=1與compact=1的語句與輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN PIPELINE header=1, graph=1, compact=1 SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 4;

EXPLAIN PIPELINE header = 1, graph = 1, compact = 1
SELECT sum(number)
FROM numbers_mt(100000)
GROUP BY number % 4

Query id: e8f88450-91da-4938-8b24-2f795e4e9f30

┌─explain────────────────────────────────────────┐
│ digraph                                        │
│ {                                              │
│   rankdir="LR";                                │
│   { node [shape = rect]                        │
│         n1 [label="NumbersMt × 4"];            │
│     subgraph cluster_0 {                       │
│       label ="Expression";                     │
│       style=filled;                            │
│       color=lightgrey;                         │
│       node [style=filled,color=white];         │
│       { rank = same;                           │
│         n5 [label="ExpressionTransform × 4"];  │
│       }                                        │
│     }                                          │
│     subgraph cluster_1 {                       │
│       label ="Expression";                     │
│       style=filled;                            │
│       color=lightgrey;                         │
│       node [style=filled,color=white];         │
│       { rank = same;                           │
│         n2 [label="ExpressionTransform × 4"];  │
│       }                                        │
│     }                                          │
│     subgraph cluster_2 {                       │
│       label ="Aggregating";                    │
│       style=filled;                            │
│       color=lightgrey;                         │
│       node [style=filled,color=white];         │
│       { rank = same;                           │
│         n3 [label="AggregatingTransform × 4"]; │
│         n4 [label="Resize"];                   │
│       }                                        │
│     }                                          │
│   }                                            │
│   n1 -> n2 [label="× 4                         │
│ number UInt64 UInt64(size = 0)"];              │
│   n2 -> n3 [label="× 4                         │
│ number UInt64 UInt64(size = 0)                 │
│ modulo(number, 4) UInt8 UInt8(size = 0)"];     │
│   n3 -> n4 [label="× 4                         │
│ modulo(number, 4) UInt8 UInt8(size = 0)        │
│ sum(number) UInt64 UInt64(size = 0)"];         │
│   n4 -> n5 [label="× 4                         │
│ modulo(number, 4) UInt8 UInt8(size = 0)        │
│ sum(number) UInt64 UInt64(size = 0)"];         │
│ }                                              │
└────────────────────────────────────────────────┘

46 rows in set. Elapsed: 0.002 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

使用EXPLAIN ESTIMATE語句顯示在處理查詢語句中，預計的有幾筆資料、標記marks以及部分parts會從資料表中讀取，這個語句可以在MergeTree家族系列的資料表引擎中執行。

首先，先建立一個資料表叫做ttt，相關的語句如下：

CREATE TABLE ttt (i Int64) ENGINE = MergeTree() ORDER BY i SETTINGS index_granularity = 16, write_final_mark = 0;
INSERT INTO ttt SELECT number FROM numbers(128);
OPTIMIZE TABLE ttt;

執行上述的語句後所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) CREATE TABLE ttt (i Int64) ENGINE = MergeTree() ORDER BY i SETTINGS index_granularity = 16, write_final_mark = 0;

CREATE TABLE ttt
(
    `i` Int64
)
ENGINE = MergeTree
ORDER BY i
SETTINGS index_granularity = 16, write_final_mark = 0

Query id: e0d60fb9-4356-4037-898c-204913a37467

Ok.

0 rows in set. Elapsed: 0.014 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) OPTIMIZE TABLE ttt;

OPTIMIZE TABLE ttt

Query id: 38317180-62b6-487a-9856-45c708d1cc65

Ok.

0 rows in set. Elapsed: 0.003 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

接著執行下列的SQL語句：

EXPLAIN ESTIMATE SELECT * FROM ttt;

執行上述的SQL語句所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN ESTIMATE SELECT * FROM ttt;

EXPLAIN ESTIMATE
SELECT *
FROM ttt

Query id: b1849a35-fabb-4654-bf01-0952a7bb360a

┌─database─┬─table─┬─parts─┬─rows─┬─marks─┐
│ default  │ ttt   │     1 │  128 │     8 │
└──────────┴───────┴───────┴──────┴───────┘

1 row in set. Elapsed: 0.001 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

EXPLAIN TABLE OVERRIDE語句則可以表示使用資料表函數對資料表綱要上的資料表覆蓋的結果，且這過程中還會進行一些驗證動作，如果覆蓋動作會導致某種失敗，則會拋出例外訊息。

假設我們有一個MySQL資料表的綱要如下：

CREATE TABLE db.example (
    id INT PRIMARY KEY,
    created DATETIME DEFAULT now()
)

將上述的SQL語句在MySQL資料庫的客戶端執行輸出的訊息如下：

mysql> CREATE TABLE db.example (
    ->     id INT PRIMARY KEY,
    ->     created DATETIME DEFAULT now()
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> use db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_db |
+--------------+
| example      |
+--------------+
1 row in set (0.00 sec)

mysql>

則在ClickHouse資料庫執行下列的SQL語句：

EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'db', 'example', 'user_name', 'password')
PARTITION BY toYYYYMM(assumeNotNull(created))

執行上述SQL語句所輸出的訊息如下：

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :) EXPLAIN TABLE OVERRIDE mysql('127.0.0.1:3306', 'db', 'example', 'user_name', 'password')
                                  PARTITION BY toYYYYMM(assumeNotNull(created))

EXPLAIN TABLE OVERRIDE
mysql('127.0.0.1:3306', 'db', 'example', 'user_name', 'password')
PARTITION BY toYYYYMM(assumeNotNull(`created`))

Query id: 901bc267-255e-47c3-9c46-6112359e443b

┌─explain─────────────────────────────────────────────────┐
│ PARTITION BY uses columns: `created` Nullable(DateTime) │
└─────────────────────────────────────────────────────────┘

1 row in set. Elapsed: 0.006 sec.

ubuntu-s-4vcpu-8gb-amd-sgp1-01 :)

因為驗證動作沒有進行完成，因此上述成功的查詢語句無法證明一個覆蓋（override）動作的執行將不會造成問題或錯誤。

結論

從本章節中我們可以知道，其他ClickHouse資料庫的SQL語句的用法，在下一章節中將會介紹ClickHouse資料庫設定檔與相關的設定。

參考資料


上一篇
day19-SQL使用與操作方法介紹(十)
下一篇
day21-ClickHouse資料庫設定檔案介紹
系列文
ClickHouse:時序資料庫建置與運行 共 30 篇
  1. 26
    day26-ClickHouse 客戶端連線介面方式(五)
  2. 27
    day27-ClickHouse 第三方客戶端連線介面方式
  3. 28
    day28-設定資料庫SSL憑證安全連線
  4. 29
    day29-設定資料庫叢集分片與複製節點
  5. 30
    day30-ClickHouse資料庫的故障排除整理
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22195 篇
完賽人數
600 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙